﻿Imports System.Data
Imports System.Data.SqlClient
Public Class ContasReceberDAL
#Region "Incluir/Alterar"
    Public Function inserir(ByVal ctrcodigo As Integer, ByVal vencodigo As Nullable(Of Integer), ByVal ccccodigo As Nullable(Of Integer), ByVal ctrdtemissao As Date, ByVal clicodigo As Nullable(Of Integer), ByVal valortotal As Decimal) As Integer
        Dim cmd = New SqlCommand
        Dim codigo As Integer
        Try
            cmd.CommandText = "execute SPContasReceber @ctr_codigo,@ven_codigo,@ccc_codigo,@ctr_dtemissao,@cli_codigo,@ctr_valortotal"
            cmd.Parameters.Add(New SqlParameter("@ctr_codigo", SqlDbType.Int)).Value = ctrcodigo
            If (vencodigo <> 0) Then
                cmd.Parameters.Add(New SqlParameter("@ven_codigo", SqlDbType.Int)).Value = vencodigo
            Else
                cmd.Parameters.Add(New SqlParameter("@ven_codigo", SqlDbType.Int)).Value = DBNull.Value
            End If
            If (ccccodigo <> 0) Then
                cmd.Parameters.Add(New SqlParameter("@ccc_codigo", SqlDbType.Int)).Value = ccccodigo
            Else
                cmd.Parameters.Add(New SqlParameter("@ccc_codigo", SqlDbType.Int)).Value = DBNull.Value
            End If
            cmd.Parameters.Add(New SqlParameter("@ctr_dtemissao", SqlDbType.DateTime)).Value = ctrdtemissao
            If (clicodigo <> 0) Then
                cmd.Parameters.Add(New SqlParameter("@cli_codigo", SqlDbType.Int)).Value = clicodigo
            Else
                cmd.Parameters.Add(New SqlParameter("@cli_codigo", SqlDbType.Int)).Value = DBNull.Value
            End If
            cmd.Parameters.Add(New SqlParameter("@ctr_valortotal", SqlDbType.Decimal)).Value = valortotal
            codigo = objconexao.executacmdAI(cmd, "Ofi_ContasReceber")
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return codigo
    End Function
#End Region
#Region "Localiza"
    Function localizar(ByVal descricao As String, tipo As Integer) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            Select Case tipo
                Case 0   'ven_codigo
                    cmd.CommandText = "select * from vw_FinContasReceber where ven_codigo = @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.Int)).Value = descricao
                Case 1 'por cod itens
                    cmd.CommandText = "select * from vw_FinContasReceber where icr_codigo = @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.Int)).Value = descricao
                Case 2    'por nome cliente
                    cmd.CommandText = "select * from vw_FinContasReceber where cli_nome COLLATE SQL_Latin1_General_CP1_CI_AI like @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & descricao & "%"
                Case 3   'por Status desc
                    cmd.CommandText = "select * from vw_FinContasReceber where sta_descricao COLLATE SQL_Latin1_General_CP1_CI_AI like @descricao order by icr_dtrecebimento "
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & descricao & "%"
                Case 4 'ctr_codigo
                    cmd.CommandText = "select * from vw_FinContasReceber where ctr_codigo= @descricao and sta_codigo <> 6"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.Int)).Value = descricao
                Case 5 'visualiza
                    cmd.CommandText = "select cli_nome,icr_parcela,icr_valor,icr_valrecebido,icr_dtvencimento,icr_dtrecebimento,ccc_descricao,sta_descricao from vw_FinContasReceber where ctr_codigo= @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.Int)).Value = descricao
                Case 6 'ctr_codigo
                    cmd.CommandText = "select * from Financeiro.Ofi_ContasReceber where ctr_codigo= @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.Int)).Value = descricao
                Case Else 'geral
                    cmd.CommandText = "select * from vw_FinContasReceber where sta_codigo =4 order by icr_dtvencimento asc"
            End Select
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
#End Region
#Region "Filtro Dt Vencimento"
    Function localizadata(ByVal dtini As Date, ByVal dtfim As Date) As DataTable 'Filtro datas
        Dim tab As DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select * from vw_FinContasReceber where  icr_dtrecebimento BETWEEN @dtini AND @dtfim"
            cmd.Parameters.Add(New SqlParameter("@dtini", SqlDbType.Date)).Value = Format(dtini, "yyyy/MM/dd")
            cmd.Parameters.Add(New SqlParameter("@dtfim", SqlDbType.Date)).Value = Format(dtfim, "yyyy/MM/dd")
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function

#End Region

End Class
